'''
Created on Sep 24, 2012

@author: peng
'''
import os
import MySQLdb

BELUGA_DB_CONFIG = {'host': '10.42.133.171',
                    'user': 'dev_analysis',
                    'password': 'analysis_dev_pwd',
                    'db': 'beluga_analysis',
                    'port': 3306}


class MySQLdbWrapper:

    conn = None

    def connect(self):
        self.conn = MySQLdb.connect(host=BELUGA_DB_CONFIG['host'],
                                    user=BELUGA_DB_CONFIG['user'],
                                    passwd=BELUGA_DB_CONFIG['password'],
                                    db=BELUGA_DB_CONFIG['db'],
                                    port=BELUGA_DB_CONFIG['port'])
        self.conn.set_character_set('utf8')
        self.conn.autocommit(True)

    def cursor(self):
        try:
            # if not self.conn:
            self.connect()
            return self.conn.cursor()
        except MySQLdb.OperationalError:
            self.connect()
            return self.conn.cursor()

_db = MySQLdbWrapper()


def get_data(sql):
    try:
        cursor = _db.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        return data
    finally:
        cursor.close()

apcv_dict = {}

country_dict = {}
language_dict = {}
access_dict = {}
carrier_dict = {}
os_version_dict = {}
resolution_dict = {}
device_model_dict = {}


path = '/mnt/export/total-user-list'


def flush(data_list):
    if len(data_list) == 0:
        return
    f = open(os.path.join(path, '20120930'), 'a+')
    data = '\n'.join(data_list)
    f.write(data)
    f.write('\n')
    f.close()


# summary
sql_0 = \
'''
select a.apcv_id, count(*)
from dev_basic a, dim_apcv_basic b
where a.apcv_id = b.apcv_id
and date(a.first_launch_time)<'2012-10-01'
group by a.apcv_id
'''

# country
sql_1 = \
'''
select a.apcv_id, a.country, count(*)
from dev_basic a, dim_apcv_basic b
where a.apcv_id = b.apcv_id
and date(a.first_launch_time)<'2012-10-01'
group by a.apcv_id, a.country
'''

# os_version
sql_2 = \
'''
select a.apcv_id, a.os_version, count(*)
from dev_basic a, dim_apcv_basic b
where a.apcv_id = b.apcv_id
and date(a.first_launch_time)<'2012-10-01'
group by a.apcv_id, a.os_version
'''

# language
sql_3 = \
'''
select a.apcv_id, a.`language`, count(*)
from dev_basic a, dim_apcv_basic b
where a.apcv_id = b.apcv_id
and date(a.first_launch_time)<'2012-10-01'
group by a.apcv_id, a.`language`
'''

# resolution
sql_4 = \
'''
select a.apcv_id, a.`resolution`, count(*)
from dev_basic a, dim_apcv_basic b
where a.apcv_id = b.apcv_id
and date(a.first_launch_time)<'2012-10-01'
group by a.apcv_id, a.`resolution`
'''


# access
sql_5 = \
'''
select a.apcv_id, a.access, count(*)
from dev_basic a, dim_apcv_basic b
where a.apcv_id = b.apcv_id
and date(a.first_launch_time)<'2012-10-01'
group by a.apcv_id, a.access
'''

# carrier
sql_6 = \
'''
select a.apcv_id, a.carrier, count(*)
from dev_basic a, dim_apcv_basic b
where a.apcv_id = b.apcv_id
and date(a.first_launch_time)<'2012-10-01'
group by a.apcv_id, a.carrier
'''

# device_model
sql_7 = \
'''
select a.apcv_id, a.device_model, count(*)
from dev_basic a, dim_apcv_basic b
where a.apcv_id = b.apcv_id
and date(a.first_launch_time)<'2012-10-01'
group by a.apcv_id, a.device_model
'''


def basic_data():
    # apcv
    apcv_dict.clear()
    sql = 'select * from dim_apcv_basic'
    rows = get_data(sql)
    for row in rows:
        if len(row[1]) != 32:
            continue
        data = [row[1], '20120930', row[2], row[3], row[4]]
        apcv_dict[row[0]] = '\t'.join(data)
    # country
    country_dict.clear()
    sql = 'select * from dim_country_basic'
    rows = get_data(sql)
    for row in rows:
        country_dict[row[0]] = row[1]
    # os version
    os_version_dict.clear()
    sql = 'select * from dim_os_version_basic'
    rows = get_data(sql)
    for row in rows:
        os_version_dict[row[0]] = row[1]
    # language
    sql = 'select * from dim_language_basic'
    rows = get_data(sql)
    for row in rows:
        language_dict[row[0]] = row[1]
    # resolution
    sql = 'select * from dim_resolution_basic'
    rows = get_data(sql)
    for row in rows:
        resolution_dict[row[0]] = row[1]
    # access
    sql = 'select * from dim_access_basic'
    rows = get_data(sql)
    for row in rows:
        access_dict[row[0]] = row[1]
    # carrier
    sql = 'select * from dim_carrier_basic'
    rows = get_data(sql)
    for row in rows:
        carrier_dict[row[0]] = row[1]
    # device_model
    sql = 'select * from dim_device_model_basic'
    rows = get_data(sql)
    for row in rows:
        device_model_dict[row[0]] = row[1]


def export_sumarry():
    data_list = []
    rows = get_data(sql_0)
    for row in rows:
        if row[0] in apcv_dict:
            line = '\t'.join([apcv_dict[row[0]], '0', '-', str(row[1])])
            data_list.append(line)
    flush(data_list)


def export_country():
    data_list = []
    rows = get_data(sql_1)
    for row in rows:
        if row[0] in apcv_dict:
            if row[1] in country_dict:
                dimension = country_dict[row[1]]
                line = '\t'.join([apcv_dict[row[0]], '1', \
                                                dimension, str(row[2])])
                data_list.append(line)
    flush(data_list)


def export_os_version():
    data_list = []
    rows = get_data(sql_2)
    for row in rows:
        if row[0] in apcv_dict:
            if row[1] in os_version_dict:
                dimension = os_version_dict[row[1]]
                line = '\t'.join([apcv_dict[row[0]], \
                                  '2', dimension, str(row[2])])
                data_list.append(line)
    flush(data_list)


def export_language():
    data_list = []
    rows = get_data(sql_3)
    for row in rows:
        if row[0] in apcv_dict:
            if row[1] in language_dict:
                dimension = language_dict[row[1]]
                line = '\t'.join([apcv_dict[row[0]], \
                                  '3', dimension, str(row[2])])
                data_list.append(line)
    flush(data_list)


def export_resolution():
    data_list = []
    rows = get_data(sql_4)
    for row in rows:
        if row[0] in apcv_dict:
            if row[1] in resolution_dict:
                dimension = resolution_dict[row[1]]
                line = '\t'.join([apcv_dict[row[0]], \
                                  '4', dimension, str(row[2])])
                data_list.append(line)
    flush(data_list)


def export_access():
    data_list = []
    rows = get_data(sql_5)
    for row in rows:
        if row[0] in apcv_dict:
            if row[1] in access_dict:
                dimension = access_dict[row[1]]
                line = '\t'.join([apcv_dict[row[0]], \
                                  '5', dimension, str(row[2])])
                data_list.append(line)
    flush(data_list)


def export_carrier():
    data_list = []
    rows = get_data(sql_6)
    for row in rows:
        if row[0] in apcv_dict:
            if row[1] in carrier_dict:
                dimension = carrier_dict[row[1]]
                line = '\t'.join([apcv_dict[row[0]], \
                                  '6', dimension, str(row[2])])
                data_list.append(line)
    flush(data_list)


def export_device_model():
    data_list = []
    rows = get_data(sql_7)
    for row in rows:
        if row[0] in apcv_dict:
            if row[1] in device_model_dict:
                dimension = device_model_dict[row[1]]
                line = '\t'.join([apcv_dict[row[0]], \
                                  '7', dimension, str(row[2])])
                data_list.append(line)
    flush(data_list)


if __name__ == '__main__':
    basic_data()
    export_sumarry()
    export_country()
    export_os_version()
    export_language()
    export_resolution()
    export_access()
    export_carrier()
    export_device_model()
